/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models.dados;

import config.JdbcConnection;
import config.LogErro;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import javax.swing.JOptionPane;
import views.utilitario.AtributosDescricao;

/**
 *
 * @author Wenderson
 */
public class JdbcDAO {
    
    private PreparedStatement ps;
    private ResultSet rs;
    
    public int count(String squema, String table) {
        try {
            String sql = "SELECT COUNT(*) FROM " + squema + "." + table;
            
            ps = JdbcConnection.getConnection().prepareCall(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            
            int count = -1;
            
            if (rs.first()) {
                count = rs.getInt(1);
            }
            
            rs.close();
            ps.close();
            
            return count;
        } catch (Exception e) {
            LogErro.gravaErro(e);e.printStackTrace();
        }
        
        return -1;
    }
    
    public int nextId(String squema, String table) {
        try {
            String sql = "SELECT IDENT_CURRENT('" + squema + "." + table + "')";
            
            ps = JdbcConnection.getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            
            int count = -1;
            
            if (rs.first()) {
                count = rs.getInt(1);
                count++;
            }
            
            rs.close();
            ps.close();
            
            return count;
        } catch (Exception e) {
            LogErro.gravaErro(e);e.printStackTrace();
        }
        
        return -1;
    }
    
    public boolean exitsTable(String squema, String table) {
        try {
            String sql = "SELECT COUNT(*) FROM " + squema + "." + table;
            
            ps = JdbcConnection.getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            
            boolean resultado = false;
            
            if (rs.first()) {
                resultado = true;
            }
            
            rs.close();
            ps.close();
            
            return resultado;
        } catch (Exception e) {
            LogErro.gravaErro(e);e.printStackTrace();
        }
        
        return false;
    }
    
    public List<AtributosDescricao> getListaAtributos(String schema, String tabela) {
        try {
            ps = JdbcConnection.getConnection().prepareStatement("SELECT TOP 1 * FROM " + schema + "." + tabela, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            rs = ps.executeQuery();

            List<AtributosDescricao> lista = new ArrayList<AtributosDescricao>();

            if (rs != null) {
                //do {
                    ResultSetMetaData resultSetMetaData = rs.getMetaData();

                    for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                        AtributosDescricao atr = new AtributosDescricao();
                        atr.setSchema(schema); //resultSetMetaData.getSchemaName(i));
                        atr.setNomeTabela(resultSetMetaData.getTableName(i));
                        atr.setNomeAtributo(resultSetMetaData.getColumnName(i));
                        atr.setTipo(resultSetMetaData.getColumnTypeName(i));
                        atr.setTamanho(resultSetMetaData.getColumnDisplaySize(i));
                        atr.setPrecisao(resultSetMetaData.getPrecision(i));
                        atr.setScala(resultSetMetaData.getScale(i));

                        lista.add(atr);
                    }
                //} while (rs.next());
            }
            
            return lista;
        } catch (Exception exception) {
            exception.printStackTrace();
        }
        return null;
    }
    
    public PreparedStatement getPreparedStatement(String sql) {
        if (sql == null || sql.trim().length() <= 5) {
            return null;
        }
        
        try {
            ps = JdbcConnection.getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            
            return ps;
        } catch (Exception e) {
            LogErro.gravaErro(e);e.printStackTrace();
        }
        return null;
    }
    
    public ResultSet executeSQL(String sql) {
        if (sql == null || sql.trim().length() <= 5) {
            return null;
        }
        
        try {
            ps = JdbcConnection.getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet result = ps.executeQuery();
            
            if (result != null && result.first()) {
                return result;
            }
            
        } catch (Exception e) {
            LogErro.gravaErro(e);e.printStackTrace();
        }
        return null;
    }    
    
    public String resultadoTabela(String esquema, String tabela) {
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * FROM ");
            sb.append(esquema);
            sb.append(".");
            sb.append(tabela);
            
            ResultSet result = this.executeSQL(sb.toString());
            String resultTabela = "NENHUM RESULTADO";
            
            if (result != null && result.first()) {
                ImprimeTabela it = new ImprimeTabela(result);
                
                resultTabela = it.imprimir();
                
                result.close();
            }
            
            return resultTabela;
        } catch (Exception e) {
            LogErro.gravaErro(e);
            e.printStackTrace();
        }
        return null;
    }
    
    public static void main(String[] args) {
        JdbcDAO j = new JdbcDAO();
        j.resultadoTabela("FrameWork.dbo", "Dimensao");
    }
    
    class ImprimeTabela {
        private ResultSet rs;
        private String tabela;
        private ArrayList<Integer> sizeColuna;
        
        public ImprimeTabela(ResultSet rs) {
            this.rs = rs;
        }
        
        public String imprimir() {
            tabela = "";
            sizeColuna = new ArrayList<Integer>();
            
            this.imprimeCabecalho();
            this.imprimeValores();
            
            return tabela;
        }
        
        private void imprimeCabecalho() {
            try {
                StringBuilder cabecalho = new StringBuilder("|");
                String traco = "";
                
                ResultSetMetaData rsmd = rs.getMetaData();
                
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    String coluna = rsmd.getColumnName(i);
                    sizeColuna.add(coluna.length());
                    
                    cabecalho.append(String.format("%2s", coluna));
                    cabecalho.append("  |");
                }
                cabecalho.append("\n");
                
                for (int i = 0; i < sizeColuna.size(); i++) {
                    traco += this.traco(sizeColuna.get(0));
                }
                
                traco += "\n";
                
                tabela = traco;
                tabela += cabecalho.toString();
                tabela += traco;
                
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        private void imprimeValores() {
            try {
                StringBuilder valores = new StringBuilder();
                
                ResultSetMetaData rsmd = rs.getMetaData();
                
                rs.first();
                
                do {                    
                    valores.append("|");
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        int sizeCol = sizeColuna.get(i - 1) + 2;
                        
                        valores.append(String.format("%" + sizeCol + "s", rs.getObject(i)));
                        valores.append("|");
                    }
                    valores.append("\n");
                } while (rs.next());
                
                tabela += valores.toString();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        private String traco(int quantidade) {
            String traco = "";
            
            for (int i = 0; i < quantidade; i++) {
                traco += "-";
            }
            
            return traco;
        }
    }
}
